const express = require('express');
const router = express.Router();
const db = require('../config/database');
const { body, validationResult } = require('express-validator');
const { authenticateToken, generateString, encryptPhone, decryptPhone, extractNumber, logUserOperation } = require('../untils/untils');
const dayjs = require('dayjs')
const { cryptoHelper } = require('../config/global');
const debug = require('debug')('app:project_category');


router.get('/', authenticateToken, async (req, res) => {
    try {
        const { category_name } = req.query;
        
        let sql = 'SELECT * FROM project_category';
        let params = [];
        
        // 模糊查询分类名称
        if (category_name && category_name.trim()) {
            sql += ' WHERE category_name LIKE ?';
            params.push(`%${category_name.trim()}%`);
        }
        
        // 添加调试信息
        console.log('🔍 查询参数:', { category_name });
        console.log('🔍 最终SQL:', sql);
        console.log('🔍 SQL参数:', params);
        
        const [result] = await db.execute(sql, params);
        
        result.forEach(item => {
            item.create_time = dayjs(item.create_time).format('YYYY-MM-DD HH:mm:ss');
        });
        await logUserOperation(req, '查询', '获取项目分类', '获取项目分类成功', '成功');
        res.json({
            success: true,
            message: '获取项目分类成功',
            data: result,
            total: result.length
        });
    } catch (error) {
        console.error('获取项目分类失败:', error);
        await logUserOperation(req, '查询', '获取项目分类', '获取项目分类失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '获取项目分类失败',
            error: error.message
        });
    }
});

// 新增项目分类
router.put('/add', authenticateToken, async (req, res) => {
    try {
        const { category_name, category_type } = req.body;
        
        // 参数验证
        if (!category_name || !category_name.trim()) {
            return res.status(400).json({
                success: false,
                message: '分类名称不能为空'
            });
        }
        
        if (!category_type || !category_type.trim()) {
            return res.status(400).json({
                success: false,
                message: '分类类型不能为空'
            });
        }
        
        // 检查分类名称是否已存在
        const [existingCategory] = await db.execute(
            'SELECT category_id FROM project_category WHERE category_name = ?',
            [category_name.trim()]
        );
        
        if (existingCategory.length > 0) {
            return res.status(400).json({
                success: false,
                message: '分类名称已存在'
            });
        }
        
        // 获取数据库中最大的category_id TY00001
        const [maxCategoryId] = await db.execute(
            'SELECT MAX(category_id) AS max_category_id FROM project_category'
        );
        let nextId = 1;
        
        if (maxCategoryId[0].max_category_id) {
            // 提取数字部分并加1
            const currentNumber = extractNumber(maxCategoryId[0].max_category_id);
            nextId = currentNumber + 1;
        }
        
        const category_id = generateString('TY', nextId, 5);
        const create_time = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const creator = req.user.userId;
        
        let sql = `INSERT INTO project_category (category_id, category_type, category_name, create_time, creator) VALUES (?, ?, ?, ?, ?)`;
        let params = [category_id, category_type.trim(), category_name.trim(), create_time, creator];
        
        const [result] = await db.execute(sql, params);
        await logUserOperation(req, '添加', '新增项目分类', '新增项目分类成功', '成功');
        res.json({
            success: true,
            message: '新增项目分类成功',
            data: {
                category_id,
                category_name: category_name.trim(),
                category_type: category_type.trim(),
                create_time,
                creator
            }
        });
    } catch (error) {
        console.error('新增项目分类失败:', error);
        await logUserOperation(req, '添加', '新增项目分类', '新增项目分类失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '新增项目分类失败',
            error: error.message
        });
    }
});

// 更新项目分类
router.post('/update', authenticateToken, async (req, res) => {
    try {
        const { category_id, category_name, category_type } = req.body;
        
        // 参数验证
        if (!category_id) {
            return res.status(400).json({
                success: false,
                message: '分类ID不能为空'
            });
        }
        
        if (!category_name || !category_name.trim()) {
            return res.status(400).json({
                success: false,
                message: '分类名称不能为空'
            });
        }
        
        if (!category_type || !category_type.trim()) {
            return res.status(400).json({
                success: false,
                message: '分类类型不能为空'
            });
        }
        
        // 检查分类是否存在
        const [existingCategory] = await db.execute(
            'SELECT category_id FROM project_category WHERE category_id = ?',
            [category_id]
        );
        
        if (existingCategory.length === 0) {
            return res.status(404).json({
                success: false,
                message: '分类不存在'
            });
        }
        
        // 检查新名称是否与其他分类重复
        const [duplicateCategory] = await db.execute(
            'SELECT category_id FROM project_category WHERE category_name = ? AND category_id != ?',
            [category_name.trim(), category_id]
        );
        
        if (duplicateCategory.length > 0) {
            return res.status(400).json({
                success: false,
                message: '分类名称已存在'
            });
        }
        
        let sql = `UPDATE project_category SET category_name = ?, category_type = ? WHERE category_id = ?`;
        let params = [category_name.trim(), category_type.trim(), category_id];
        
        const [result] = await db.execute(sql, params);
        
        if (result.affectedRows === 0) {
            return res.status(404).json({
                success: false,
                message: '更新失败，分类不存在'
            });
        }
        await logUserOperation(req, '更新', '更新项目分类', '更新项目分类成功', '成功');
        res.json({
            success: true,
            message: '更新项目分类成功',
            data: {
                category_id,
                category_name: category_name.trim(),
                category_type: category_type.trim()
            }
        });
    } catch (error) {
        console.error('更新项目分类失败:', error);
        await logUserOperation(req, '更新', '更新项目分类', '更新项目分类失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '更新项目分类失败',
            error: error.message
        });
    }
});

// 删除项目分类
router.delete('/delete', authenticateToken, async (req, res) => {
    try {
        console.log('删除请求参数:', req.query);
        const { category_id } = req.query;
        
        // 参数验证
        if (!category_id) {
            return res.status(400).json({
                success: false,
                message: '分类ID不能为空'
            });
        }
        
        // 检查分类是否存在
        const [existingCategory] = await db.execute(
            'SELECT category_id, category_name FROM project_category WHERE category_id = ?',
            [category_id]
        );
        
        if (existingCategory.length === 0) {
            return res.status(404).json({
                success: false,
                message: '分类不存在'
            });
        }
        
        // 检查是否有关联的项目（可选，根据业务需求）
        // const [relatedProjects] = await db.execute(
        //     'SELECT COUNT(*) as count FROM projects WHERE category_id = ?',
        //     [category_id]
        // );
        // 
        // if (relatedProjects[0].count > 0) {
        //     return res.status(400).json({
        //         success: false,
        //         message: '该分类下还有项目，无法删除'
        //     });
        // }
        
        let sql = `DELETE FROM project_category WHERE category_id = ?`;
        let params = [category_id];
        
        const [result] = await db.execute(sql, params);
        
        if (result.affectedRows === 0) {
            return res.status(404).json({
                success: false,
                message: '删除失败，分类不存在'
            });
        }
        await logUserOperation(req, '删除', '删除项目分类', '删除项目分类成功', '成功');
        res.json({
            success: true,
            message: '删除项目分类成功',
            data: {
                category_id,
                category_name: existingCategory[0].category_name,
                deleted: true
            }
        });
    } catch (error) {
        console.error('删除项目分类失败:', error);
        await logUserOperation(req, '删除', '删除项目分类', '删除项目分类失败', '失败', error.message);
        res.status(500).json({
            success: false,
            message: '删除项目分类失败',
            error: error.message
        });
    }
});

module.exports = router;